
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 02/24/2011 17:31:30
-- Generated from EDMX file: D:\VMDS\VMDS\VMDS.Data.SQL\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [VMDS];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_VMSubject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VMs] DROP CONSTRAINT [FK_VMSubject];
GO
IF OBJECT_ID(N'[dbo].[FK_VMOS]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VMs] DROP CONSTRAINT [FK_VMOS];
GO
IF OBJECT_ID(N'[dbo].[FK_VMStorage_VM]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VMStorage] DROP CONSTRAINT [FK_VMStorage_VM];
GO
IF OBJECT_ID(N'[dbo].[FK_VMStorage_Storage]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VMStorage] DROP CONSTRAINT [FK_VMStorage_Storage];
GO
IF OBJECT_ID(N'[dbo].[FK_OSStorage_OS]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OSStorage] DROP CONSTRAINT [FK_OSStorage_OS];
GO
IF OBJECT_ID(N'[dbo].[FK_OSStorage_Storage]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OSStorage] DROP CONSTRAINT [FK_OSStorage_Storage];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskClientPC]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tasks] DROP CONSTRAINT [FK_TaskClientPC];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskVM]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tasks] DROP CONSTRAINT [FK_TaskVM];
GO
IF OBJECT_ID(N'[dbo].[FK_ScreenShotVM]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScreenShots] DROP CONSTRAINT [FK_ScreenShotVM];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[ClientPCs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClientPCs];
GO
IF OBJECT_ID(N'[dbo].[Subjects]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Subjects];
GO
IF OBJECT_ID(N'[dbo].[OSs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OSs];
GO
IF OBJECT_ID(N'[dbo].[Storages]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Storages];
GO
IF OBJECT_ID(N'[dbo].[VMs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[VMs];
GO
IF OBJECT_ID(N'[dbo].[Tasks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tasks];
GO
IF OBJECT_ID(N'[dbo].[ScreenShots]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ScreenShots];
GO
IF OBJECT_ID(N'[dbo].[VMStorage]', 'U') IS NOT NULL
    DROP TABLE [dbo].[VMStorage];
GO
IF OBJECT_ID(N'[dbo].[OSStorage]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OSStorage];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'ClientPCs'
CREATE TABLE [dbo].[ClientPCs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [IP] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Subjects'
CREATE TABLE [dbo].[Subjects] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'OSs'
CREATE TABLE [dbo].[OSs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Version] int  NOT NULL
);
GO

-- Creating table 'Storages'
CREATE TABLE [dbo].[Storages] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Path] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'VMs'
CREATE TABLE [dbo].[VMs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [SubjectId] int  NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [Version] int  NOT NULL,
    [OSId] int  NULL,
    [CreatedBy] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL
);
GO

-- Creating table 'Tasks'
CREATE TABLE [dbo].[Tasks] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ClientPCId] int  NOT NULL,
    [VMId] int  NOT NULL,
    [CreatedBy] nvarchar(max)  NOT NULL,
    [Comleted] bit  NOT NULL,
    [State_Value] int  NOT NULL,
    [Priority_Value] int  NOT NULL
);
GO

-- Creating table 'ScreenShots'
CREATE TABLE [dbo].[ScreenShots] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [VMId] int  NOT NULL,
    [FileName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'VMStorage'
CREATE TABLE [dbo].[VMStorage] (
    [VMs_Id] int  NOT NULL,
    [Storages_Id] int  NOT NULL
);
GO

-- Creating table 'OSStorage'
CREATE TABLE [dbo].[OSStorage] (
    [OS_Id] int  NOT NULL,
    [Storages_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'ClientPCs'
ALTER TABLE [dbo].[ClientPCs]
ADD CONSTRAINT [PK_ClientPCs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Subjects'
ALTER TABLE [dbo].[Subjects]
ADD CONSTRAINT [PK_Subjects]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'OSs'
ALTER TABLE [dbo].[OSs]
ADD CONSTRAINT [PK_OSs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Storages'
ALTER TABLE [dbo].[Storages]
ADD CONSTRAINT [PK_Storages]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'VMs'
ALTER TABLE [dbo].[VMs]
ADD CONSTRAINT [PK_VMs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [PK_Tasks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ScreenShots'
ALTER TABLE [dbo].[ScreenShots]
ADD CONSTRAINT [PK_ScreenShots]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [VMs_Id], [Storages_Id] in table 'VMStorage'
ALTER TABLE [dbo].[VMStorage]
ADD CONSTRAINT [PK_VMStorage]
    PRIMARY KEY NONCLUSTERED ([VMs_Id], [Storages_Id] ASC);
GO

-- Creating primary key on [OS_Id], [Storages_Id] in table 'OSStorage'
ALTER TABLE [dbo].[OSStorage]
ADD CONSTRAINT [PK_OSStorage]
    PRIMARY KEY NONCLUSTERED ([OS_Id], [Storages_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [SubjectId] in table 'VMs'
ALTER TABLE [dbo].[VMs]
ADD CONSTRAINT [FK_VMSubject]
    FOREIGN KEY ([SubjectId])
    REFERENCES [dbo].[Subjects]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VMSubject'
CREATE INDEX [IX_FK_VMSubject]
ON [dbo].[VMs]
    ([SubjectId]);
GO

-- Creating foreign key on [OSId] in table 'VMs'
ALTER TABLE [dbo].[VMs]
ADD CONSTRAINT [FK_VMOS]
    FOREIGN KEY ([OSId])
    REFERENCES [dbo].[OSs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VMOS'
CREATE INDEX [IX_FK_VMOS]
ON [dbo].[VMs]
    ([OSId]);
GO

-- Creating foreign key on [VMs_Id] in table 'VMStorage'
ALTER TABLE [dbo].[VMStorage]
ADD CONSTRAINT [FK_VMStorage_VM]
    FOREIGN KEY ([VMs_Id])
    REFERENCES [dbo].[VMs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Storages_Id] in table 'VMStorage'
ALTER TABLE [dbo].[VMStorage]
ADD CONSTRAINT [FK_VMStorage_Storage]
    FOREIGN KEY ([Storages_Id])
    REFERENCES [dbo].[Storages]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VMStorage_Storage'
CREATE INDEX [IX_FK_VMStorage_Storage]
ON [dbo].[VMStorage]
    ([Storages_Id]);
GO

-- Creating foreign key on [OS_Id] in table 'OSStorage'
ALTER TABLE [dbo].[OSStorage]
ADD CONSTRAINT [FK_OSStorage_OS]
    FOREIGN KEY ([OS_Id])
    REFERENCES [dbo].[OSs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Storages_Id] in table 'OSStorage'
ALTER TABLE [dbo].[OSStorage]
ADD CONSTRAINT [FK_OSStorage_Storage]
    FOREIGN KEY ([Storages_Id])
    REFERENCES [dbo].[Storages]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OSStorage_Storage'
CREATE INDEX [IX_FK_OSStorage_Storage]
ON [dbo].[OSStorage]
    ([Storages_Id]);
GO

-- Creating foreign key on [ClientPCId] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [FK_TaskClientPC]
    FOREIGN KEY ([ClientPCId])
    REFERENCES [dbo].[ClientPCs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskClientPC'
CREATE INDEX [IX_FK_TaskClientPC]
ON [dbo].[Tasks]
    ([ClientPCId]);
GO

-- Creating foreign key on [VMId] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [FK_TaskVM]
    FOREIGN KEY ([VMId])
    REFERENCES [dbo].[VMs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskVM'
CREATE INDEX [IX_FK_TaskVM]
ON [dbo].[Tasks]
    ([VMId]);
GO

-- Creating foreign key on [VMId] in table 'ScreenShots'
ALTER TABLE [dbo].[ScreenShots]
ADD CONSTRAINT [FK_ScreenShotVM]
    FOREIGN KEY ([VMId])
    REFERENCES [dbo].[VMs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScreenShotVM'
CREATE INDEX [IX_FK_ScreenShotVM]
ON [dbo].[ScreenShots]
    ([VMId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------